/**
 * Generate an Excel test file for V2 menu import with both valid and intentionally invalid rows.
 * Output: backend/tmp/menu_v2_test.xlsx
 */
const fs = require('fs');
const fsp = require('fs').promises;
const path = require('path');
const XLSX = require('xlsx');
const { SCHEMA } = require('../src/constants/importV2Schema');

(async () => {
  try {
    const outDir = path.join(__dirname, '../tmp');
    await fsp.mkdir(outDir, { recursive: true });
    const outPath = path.join(outDir, 'menu_v2_test.xlsx');

    // Helper to convert row object to array matching headers order
    const toRowArray = (headers, obj) => headers.map(h => (obj[h] ?? ''));

    // Build sheets
    const dishesHeaders = SCHEMA.Dishes;
    const variantsHeaders = SCHEMA.Variants;
    const optionsHeaders = SCHEMA.Options;

    const dishesRows = [
      // Valid row (auto slug warning expected)
      {
        dish_sku: 'SKU-001',
        dish_slug: '',
        name_zh: '烤鸡饭',
        name_en: 'Grilled Chicken Rice',
        category: 'AutoTestCat',
        base_status: 'active',
        description_zh: '香嫩烤鸡配米饭',
        description_en: 'Juicy grilled chicken with rice',
        ingredients_zh: '鸡肉, 米饭',
        ingredients_en: 'chicken, rice',
        tags: 'grilled,protein',
        popularity: '90'
      },
      // Valid row (explicit slug)
      {
        dish_sku: 'SKU-002',
        dish_slug: 'beef-noodle',
        name_zh: '牛肉面',
        name_en: 'Beef Noodle',
        category: 'AutoTestCat',
        base_status: 'inactive',
        description_zh: '经典牛肉面',
        description_en: 'Classic beef noodle',
        ingredients_zh: '牛肉, 面',
        ingredients_en: 'beef, noodle',
        tags: 'spicy',
        popularity: ''
      },
      // Invalid row: duplicate SKU, invalid slug pattern, missing name_en, non-existent category, invalid base_status, popularity out of range
      {
        dish_sku: 'SKU-002',
        dish_slug: 'bad slug!!',
        name_zh: '错误示例',
        name_en: '',
        category: 'NoSuchCat',
        base_status: 'enabled',
        description_zh: '',
        description_en: '',
        ingredients_zh: '',
        ingredients_en: '',
        tags: 'a,b,c',
        popularity: '101'
      }
    ];

    const variantsRows = [
      // Valid
      {
        dish_sku: 'SKU-001',
        name_zh: '小份',
        variant_code: 'S-6P',
        variant_name: '6 pieces',
        price: '12.5',
        compare_at_price: '',
        is_default: 'true',
        stock: '100'
      },
      // Invalid: unknown SKU, non-numeric price, invalid boolean, negative stock
      {
        dish_sku: 'SKU-XXX',
        name_zh: '',
        variant_code: 'X-9P',
        variant_name: '9 pieces',
        price: 'abc',
        compare_at_price: '',
        is_default: 'maybe',
        stock: '-5'
      },
      // Invalid: missing required variant_code
      {
        dish_sku: 'SKU-001',
        name_zh: '',
        variant_code: '',
        variant_name: 'No Code',
        price: '',
        compare_at_price: '',
        is_default: '',
        stock: ''
      }
    ];

    const optionsRows = [
      // Valid
      {
        dish_sku: 'SKU-001',
        name_zh: '',
        group_code: 'MEAT',
        group_name: 'Meat',
        required: 'false',
        value_code: 'BEEF',
        value_name: 'Beef',
        price_delta: '2'
      },
      // Invalid: unknown SKU, invalid boolean, missing required value_name (and negative price is allowed)
      {
        dish_sku: 'SKU-YYY',
        name_zh: '',
        group_code: 'SPICE',
        group_name: 'Spice Level',
        required: 'ok',
        value_code: 'LVL3',
        value_name: '',
        price_delta: '-1'
      },
      // Invalid: missing required group_code
      {
        dish_sku: 'SKU-001',
        name_zh: '',
        group_code: '',
        group_name: 'Sauce',
        required: '',
        value_code: 'SOY',
        value_name: 'Soy Sauce',
        price_delta: ''
      }
    ];

    const dishesAoA = [dishesHeaders, ...dishesRows.map(r => toRowArray(dishesHeaders, r))];
    const variantsAoA = [variantsHeaders, ...variantsRows.map(r => toRowArray(variantsHeaders, r))];
    const optionsAoA = [optionsHeaders, ...optionsRows.map(r => toRowArray(optionsHeaders, r))];

    const wb = XLSX.utils.book_new();
    const wsDishes = XLSX.utils.aoa_to_sheet(dishesAoA);
    const wsVariants = XLSX.utils.aoa_to_sheet(variantsAoA);
    const wsOptions = XLSX.utils.aoa_to_sheet(optionsAoA);

    XLSX.utils.book_append_sheet(wb, wsDishes, 'Dishes');
    XLSX.utils.book_append_sheet(wb, wsVariants, 'Variants');
    XLSX.utils.book_append_sheet(wb, wsOptions, 'Options');

    XLSX.writeFile(wb, outPath);

    console.log(`Generated: ${outPath}`);
  } catch (err) {
    console.error('Failed to generate test Excel:', err);
    process.exitCode = 1;
  }
})();